﻿/***********************************************************************************************************************************
 *    Name: Job_Duration.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE msdb;
SELECT  c.name,
        j.name,
        h.step_id,
        h.step_name,
        start_time = dbo.agent_datetime(h.run_date, h.run_time),
        run_duration = CAST(DATEADD(SECOND,h.run_duration / 10000 * 3600 + (h.run_duration % 10000) / 100 * 60 + h.run_duration % 100 , 0) AS TIME(0))	-- HHMMSS -> Seconds
FROM    dbo.sysjobhistory h
        INNER JOIN dbo.sysjobs j ON (h.job_id = j.job_id)
        INNER JOIN dbo.syscategories c ON (j.category_id = c.category_id)
WHERE   c.category_class = 1
        AND c.name = N'DBAdmin'
        AND h.step_id = 0
ORDER BY c.name,
        j.name,
        start_time DESC,
        h.step_id DESC;